# coding=utf-8
import sys
from PyQt4 import QtGui,QtCore,uic,QtSql
# import sqlite3
import xlrd

# pyqt + sqlite3 将数据库的内容显示到tableview上

myWindowFile = uic.loadUiType("eSimManageMentTool.ui")[0]
myLoadWindowFile = uic.loadUiType("load.ui")[0]
myUpDataWindowFile = uic.loadUiType("updata.ui")[0]

class myUpDataWindow(QtGui.QWidget,myUpDataWindowFile):
    def __init__(self, parent=None):
        QtGui.QWidget.__init__(self,parent)
        self.setupUi(self)
        self.pushButton.clicked.connect(self.myUpDate)
        self.preText = self.comboBox.currentText()

    def myUpDate(self):
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("eSIM.db")
        self.db.open()
        self.query = QtSql.QSqlQuery()
        # sql  语句用单引号括起来,执行成功,错误代码-1,数据库实际并不受影响
        # 改成这样数据库修改成功
        self.updataSql = '''update eSIM set CardStatus = "%s",Comment=datetime("now","localtime")||"　%s" where "PhoneNo" = "%s"'''%(self.comboBox.currentText(),self.textEdit.toPlainText(),self.lineEdit_2.text())
        self.query.prepare(self.updataSql)
        print(self.updataSql)
        if self.query.exec_() == True:
            print(self.query.lastError().number())
            updataResult = QtGui.QMessageBox.information(self, "更新成功", "成功将'%s'状态从\n'%s'\n更改成\n'%s'\n"%(self.lineEdit.text(),self.preText,self.comboBox.currentText()))
            if updataResult == QtGui.QMessageBox.Ok:
                self.close()
        else:
            print(self.query.lastError().number())
        self.db.close()


class myLoadWindow(QtGui.QDialog,myLoadWindowFile):
    def __init__(self,parent=None):
        QtGui.QDialog.__init__(self,parent)
        self.setupUi(self)
        self.pushButton.clicked.connect(self.openFile)
        self.pushButton_2.clicked.connect(self.loadData)

    def openFile(self):
        self.filePath = QtGui.QFileDialog.getOpenFileName().replace('/','\\')
        self.lineEdit.setText(self.filePath)
    def loadData(self):
        # 打开数据库,如果数据库文件不存在,就直接创建文件
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("eSIM.db")
        # 因为没有执行 db.open() 所以tableview中没有内容显示
        self.db.open()
        self.query = QtSql.QSqlQuery()
        self.book = xlrd.open_workbook(self.filePath)
        self.sheet = self.book.sheet_by_index(0)
        self.rowCount = self.sheet.nrows
        print(self.rowCount)
        self.progressBar.setMaximum(self.rowCount)
        for i in range(1,self.rowCount):
            rowData = self.sheet.row_values(i)
            # insert_sql = "insert into eSIM(ICCID,PhoneNo) values(?,?)"
            self.query.prepare("insert into eSIM(ICCID,PhoneNo) values('%s','%s')" % (rowData[0], rowData[1]))
            # 下面这种写法一直不成功,原来是使用sql 语句中，如果有字符串，必须加上''单括号括起来
            # self.query.exec_("insert into eSIM(PhoneNo,ICCID ) values(%s,%s)" % (iccid, phoneNo))
            # 还有一种没成功的写法
            # self.query.exec_("insert into eSIM(PhoneNo,ICCID ) values(?,?)" % (iccid, phoneNo))
            # 不知道是不是加上引号之后也可以执行成功
            if self.query.exec_() != True:
                if self.query.lastError().number() == 19:
                    QtGui.QMessageBox.information(self, "错误", 'ICCID:'+rowData[1]+'已存在,\n请确认数据是否导入过,推出本次操作!!!')
                    break
                else:
                    QtGui.QMessageBox.information(self, "错误", self.query.lastError().databaseText())
            self.progressBar.setValue(i)
        else:
            loadDone = QtGui.QMessageBox.information(self, "成功", "成功导入%d条数据" % (self.rowCount - 1))
            if loadDone == QtGui.QMessageBox.Ok:
                self.close()
        self.db.close()

        # QtGui.QMessageBox.information(self, "Information",self.tr("填写任意想告诉于用户的信息!"))


class myMainWindow(QtGui.QMainWindow,myWindowFile):
    def __init__(self,parent=None):
        QtGui.QMainWindow.__init__(self,parent)
        self.setupUi(self)
        self.dataOffset = 0
        self.select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus","Comment" from "eSIM" limit 100'
        self.loadUi = myLoadWindow()
        self.tableView.resizeColumnsToContents()
        self.pushButton_5.clicked.connect(self.loadUi.show)
        self.pushButton_6.clicked.connect(self.refresh)
        self.pushButton_8.clicked.connect(self.nextPage)
        self.pushButton_7.clicked.connect(self.previousPage)
        self.pushButton.clicked.connect(self.selectData)
        self.pushButton_2.clicked.connect(self.selectICCID)
        self.pushButton_3.clicked.connect(self.selectPhoneNo)
        self.pushButton_4.clicked.connect(self.selectCardStatus)
        self.tableView.doubleClicked.connect(self.updata)
        #打开数据库,如果数据库文件不存在,就直接创建文件
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("eSIM.db")
        # 因为没有执行 db.open() 所以tableview中没有内容显示
        self.db.open()

        #在数据库中常见一个表
        create_table_sql = '''create table if not exists eSIM(
                                                    ICCID text primary key,
                                                    PhoneNo text not null,
                                                    StorageDate text default (date('now','localtime')),
                                                    StorageTime text default (time('now','localtime')),
                                                    CardStatus text default '正常',
                                                    Comment text)'''
        # 建表命令失败的原因是   最后 '正常' 写成了u'正常'
        self.query = QtSql.QSqlQuery()
        self.query.prepare(create_table_sql)
        if self.query.exec_() != True:
            print(self.query.lastError().databaseText())
        self.db.close()
        self.displayData()


    def refresh(self):
        self.select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus","Comment" from "eSIM" limit 100'
        self.dataOffset = 0
        self.displayData()

    def modleSet(self):
        self.modle.setHeaderData(0, QtCore.Qt.Horizontal, "ICCID")
        self.modle.setHeaderData(1, QtCore.Qt.Horizontal, "手机号")
        self.modle.setHeaderData(2, QtCore.Qt.Horizontal, "入库日期")
        self.modle.setHeaderData(3, QtCore.Qt.Horizontal, "状态")
        self.modle.setHeaderData(4, QtCore.Qt.Horizontal, "备注")
        self.tableView.setModel(self.modle)
        self.statusbar.showMessage("当前显示%d条记录,偏移地址%d" % (self.modle.rowCount(), self.dataOffset))

    def displayData(self):
        #设置tableview的Model 将数据库中指定的信息显示到tableview上
        # select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus" from "eSIM" limit 100 offset %d'
        # self.select_sql = self.select_sql + " offset %d" % (self.dataOffset)
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("eSIM.db")
        self.db.open()
        self.modle = QtSql.QSqlQueryModel(self.tableView)
        self.modle.setQuery(self.select_sql + " offset %d" % (self.dataOffset))
        print(self.select_sql + " offset %d" % (self.dataOffset))
        self.modleSet()
        self.db.close()

    def nextPage(self):
        self.dataOffset = self.dataOffset+100
        # 设置tableview的Model 将数据库中指定的信息显示到tableview上
        # select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus" from "eSIM" limit 100 offset %d '
        # self.select_sql = self.select_sql + " offset %d" % (self.dataOffset)
        self.db.open()
        self.modle = QtSql.QSqlQueryModel(self.tableView)
        self.modle.setQuery(self.select_sql + " offset %d" % (self.dataOffset))
        if self.modle.rowCount() == 0:
            QtGui.QMessageBox.information(self, "提示", "已经是最后一页")
            self.dataOffset = self.dataOffset - 100
            self.modle.setQuery(self.select_sql + " offset %d" % (self.dataOffset))

        self.modleSet()
        self.db.close()

    def previousPage(self):
        if self.dataOffset > 0:
            self.dataOffset = self.dataOffset-100
        else:
            QtGui.QMessageBox.information(self, "提示", "已经是第一页")
        # 设置tableview的Model 将数据库中指定的信息显示到tableview上
        # select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus" from "eSIM" limit 100 offset %d '
        # self.select_sql = self.select_sql + " offset %d" % (self.dataOffset)
        self.db.open()
        self.modle = QtSql.QSqlQueryModel(self.tableView)
        self.modle.setQuery(self.select_sql + " offset %d" % (self.dataOffset))
        self.modleSet()
        self.tableView.setModel(self.modle)
        self.statusbar.showMessage("当前显示%d条记录,偏移地址%d" % (self.modle.rowCount(),self.dataOffset))
        self.db.close()

    def selectData(self):
        sDate = self.dateEdit.date().toString("yyyy-MM-dd")
        self.dataOffset = 0
        self.select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus","Comment" from "eSIM" where "StorageDate" = "%s" limit 100'%(sDate)
        print(self.select_sql)
        self.displayData()
    def selectICCID(self):
        sICCID = self.lineEdit.text()
        print(sICCID)
        self.dataOffset = 0
        self.select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus","Comment" from "eSIM" where "ICCID" = "%s" limit 100'%(sICCID)
        print(self.select_sql)
        self.displayData()
    def selectPhoneNo(self):
        sPhoneNo = self.lineEdit_2.text()
        print(sPhoneNo)
        self.dataOffset = 0
        self.select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus","Comment" from "eSIM" where "PhoneNo" = "%s" limit 100'%(sPhoneNo)
        print(self.select_sql)
        self.displayData()
    def selectCardStatus(self):
        sCardStatus = self.comboBox.currentText()
        print(sCardStatus)
        self.dataOffset = 0
        self.select_sql = 'select ICCID,"PhoneNo","StorageDate","CardStatus","Comment" from "eSIM" where "CardStatus" = "%s" limit 100'%(sCardStatus)
        print(self.select_sql)
        self.displayData()

    def updata(self):
        cRow= self.tableView.currentIndex().row()
        model = self.tableView.model()
        # index_a = model.index(cIndex.row(),2)
        self.upDataUi = myUpDataWindow()
        self.upDataUi.lineEdit.setText(model.index(cRow,0).data())
        self.upDataUi.lineEdit_2.setText(model.index(cRow,1).data())
        self.upDataUi.comboBox.setCurrentText(model.index(cRow,3).data())
        # 这个条件判断是因为当单元格数据为空时,model.index(cRow,4).data()返回 PyQt4.QtCore.QPyNullVariant 类,造成textEdit设置数据出错
        if isinstance(model.index(cRow,4).data(),str):
            self.upDataUi.textEdit.setPlainText(model.index(cRow,4).data())
        self.upDataUi.show()


def main():
    app = QtGui.QApplication(sys.argv)
    w = myMainWindow()
    w.show()
    app.exec_()




if __name__ == '__main__':
    main()



